// AnnouncementsDao.java
package org.example.dao;

import org.example.pojo.Announcement;
import org.example.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AnnouncementsDao {

    // 发布公告
    public void addAnnouncement(String title, String content) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "INSERT INTO announcements(title, content, create_time, update_time) VALUES (?, ?, NOW(), NOW())";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, title);
        ps.setString(2, content);
        ps.executeUpdate();
    }

    // 查看所有公告
    public List<Announcement> getAnnouncements() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "SELECT * FROM announcements ORDER BY create_time DESC";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        List<Announcement> announcements = new ArrayList<>();
        while (rs.next()) {
            Announcement announcement = new Announcement();
            announcement.setId(rs.getInt("id"));
            announcement.setTitle(rs.getString("title"));
            announcement.setContent(rs.getString("content"));
            announcement.setCreateTime(rs.getTimestamp("create_time"));
            announcement.setUpdateTime(rs.getTimestamp("update_time"));
            announcements.add(announcement);
        }
        return announcements;
    }

    // 修改公告
    public void updateAnnouncement(int id, String title, String content) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "UPDATE announcements SET title = ?, content = ?, update_time = NOW() WHERE id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, title);
        ps.setString(2, content);
        ps.setInt(3, id);
        ps.executeUpdate();
    }

    // 删除公告
    public void deleteAnnouncement(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "DELETE FROM announcements WHERE id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        ps.executeUpdate();
    }

    public  Announcement getAnnouncement(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "SELECT * FROM announcements where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1,id);
        ResultSet rs = ps.executeQuery();
        Announcement a=new Announcement();
        while (rs.next()) {
            a.setId(rs.getInt("id"));
            a.setTitle(rs.getString("title"));
            a.setContent(rs.getString("content"));
            a.setCreateTime(rs.getTimestamp("create_time"));
            a.setUpdateTime(rs.getTimestamp("update_time"));
        }
        return a;
    }
}
